package com.xinyu;

import com.xinyu.domain.Alarm;
import com.xinyu.domain.Words;
import com.xinyu.mapper.AlarmMapper;
import com.xinyu.mapper.WordsMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.sql.*;

@SpringBootTest(classes = HelperproApplication.class)
public class SQLiteToExcel {

    @Autowired
    private WordsMapper wordsMapper;

    @Autowired
    private AlarmMapper alarmMapper;

    @Test
    public void main() {
        // SQLite 数据库文件路径
        String dbPath = "D:\\EMCS\\EMCServer\\database\\AdminFile\\EmcsAdmin.db";

        // 连接数据库
        try (Connection connection = DriverManager.getConnection("jdbc:sqlite:" + dbPath);
             Statement statement = connection.createStatement()) {

            // 查询数据
            String query = "SELECT * FROM tbdEAlarm"; // 替换为你的表名
            ResultSet resultSet = statement.executeQuery(query);

            // 将数据导出为 Excel
            printResultSet(resultSet);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 将 ResultSet 中的数据打印到控制台
     *
     * @param resultSet 查询结果集
     * @throws Exception 如果发生错误
     */
    public void printResultSet(ResultSet resultSet) throws Exception {
        // 获取结果集的元数据（列名和列数）
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();

        // 打印表头（列名）
        for (int i = 1; i <= columnCount; i++) {
            System.out.print(metaData.getColumnName(i) + "\t"); // 用制表符分隔列
        }
        System.out.println(); // 换行

        // 打印数据行
        while (resultSet.next()) {
//            Words words = new Words();
//            words.setId(resultSet.getInt(1));
//            words.setWord(resultSet.getString(2));
//            words.setPronunciation(resultSet.getString(3));
//            words.setExplaination(resultSet.getString(4));
//            words.setTermmeaning(resultSet.getString(5));
//            words.setRemark(resultSet.getString(6));
//            System.out.println(words);
//
//            wordsMapper.insert(words);

            Alarm alarm = new Alarm();
            alarm.setId(resultSet.getInt(1));
            alarm.setAlarm(resultSet.getString(3));
            alarm.setMeaning(resultSet.getString(5));
            alarm.setMeasures(resultSet.getString(6));
            alarm.setRemark(resultSet.getString(7));
            System.out.println(alarm);

            alarmMapper.insert(alarm);

        }
    }
}